A dynamic table
This example will generate a dynamic table, with one row added for each attached record (see below for an illustration of this).
In this example we add a single row table with three columns.
The code for this example is:
Private Sub Document_Open()
'
' Setup the data source and run the merge
Set MainDocument = ActiveDocument
With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="", _
connection:="DSN=EMu Loans;", _
SQLStatement:="SELECT * FROM eloans.csv", _
Subtype:=wdMergeSubTypeWord2000
.Destination = wdSendToNewDocument
.Execute Pause:=False
End With
Dim key As String
Dim row As Integer
Dim table As Word.table
Dim connection As New ADODB.connection
Dim recordset As New ADODB.recordset
'
' Connect to the DSN that we are going to use
connection.Open "DSN=EMu Loans;"
'
' This code cycles through each table we have added to the Word document in order to populate
' it with data. In this example we have only one table
For i = 1 To ActiveDocument.Tables.Count
With ActiveDocument.Tables(i)
'
' Copy and then modify the following "If" section of code (between the If and
' End If statements) for each .csv file generated.
If InStr(.Cell(1, 1).Range.Text, "IRN") = 1 Then
'
' Identify the key - in this case it is located in row 1, column 2
key = .Cell(1, 2).Range.Text
key = Trim(Left(key, Len(key) - 2))
'
' We want to display the IRN and Summary Data.
' Because the number of attached Catalogue records is unknown, we use the
' following code to add a row dynamically for each record.
row = 1
'
' You would replace the name of the .csv file and key as required
' by your report.
' In this example the .csv file is called ObjObjec.csv
' and the key is eloans_key
recordset.Open "select * from ObjObjec.csv where eloans_key = " & key, connection
While Not recordset.EOF
row = row + 1
.Rows.Add
.Cell(row, 1).Range.Text = recordset("irn")
.Cell(row, 3).Range.Text = recordset("SummaryData")
recordset.MoveNext
Wend
recordset.Close
'
' Remove the column containing the key so that it doesn't display
' in the mail merge report
.Columns(2).Select
Selection.Columns.Delete
' Remove the table borders
.Borders.Enable = False
End If
End With
Next i
'
' Close down our connections
connection.Close
'
' Force all fields to be updated in case we have images
ActiveDocument.Fields.Update
' MainDocument.Close SaveChanges:=False
End Sub
Private Sub Document_Close()
' When the document is closed we need to disconnect the
' data source otherwise Word will try and use it
' when opening the document
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.Save
End Sub
The Visual Basic Editor will appear similar to this:
- Save and close the main report document.
- At Step 1. Write the VB code we modified the VB code for testing purposes by commenting out
MainDocument.Close SaveChanges:=False
. Before we finalize the report we need to edit the VB code to remove the apostrophe.When the report is run, the following mail merge report will be generated, with a row added to the Objects table for each linked object: